--TABLE WOULD LOOSELY REPRESENT YOUR TABLE.  NOTICE THE SomeText FIELD IS EXPLICITELY TEXT DATATYPE
DECLARE @T TABLE (ID INT PRIMARY KEY IDENTITY(1,1), SomeText TEXT)

INSERT INTO @T (SomeText) VALUES 
('This is a lot of text with a word abcMEDICAREdef we need to use substring on'),
('This is a lot of text with a word abcKELLYdef we need to use substring on')

--THIS SHOULD HAVE SLOWER PERFORMANCE BECAUSE IT REQUIRES CASTING EACH RECORD
SELECT	ID,
		CAST(SomeText AS XML)
FROM	@T


--THIS IS BROKEN OUT AS A VARIABLE JUST TO DEMONSTRATE THE SYNTAX IN A STEPWISE WAY.  Consider this all inline to the CTE.
--BE aweare of failures due to atomic values.
DECLARE @XML XML = 
							(
								SELECT	ID AS "@ID",
										SomeText AS "@SomeXML"
								FROM	@T
								FOR XML PATH('RECORD'), ROOT('ROOT')
							)

--THIS SHOWS THE XML CONTENT
SELECT @XML

--THIS Does the actual contains query filter, shredds back to a table, and joins to the original data on ID (A key in your case)
;WITH QueriedData AS (
	SELECT	ID = FieldAlias.value('(@ID)[1]','BIGINT')
	FROM	@XML.nodes('/ROOT/RECORD') AS TableAlias(FieldAlias)
	WHERE	FieldAlias.exist('@SomeXML[fn:contains(.,"MEDICARE")]') = 1
)
SELECT	T.*
FROM	QueriedData AS QD
		INNER JOIN @T AS T ON (QD.ID = T.ID)



